package com.neuedu.mblog.dao;

import com.neuedu.mblog.entiry.Post;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
* 项目：his
* 创建时间：  2021-12-28 15:47:16
* 作者 :金山老师
* 描述 : 数据访问层
*/
public class PostDao extends BaseDao<Post> {

    private QueryRunner run = new QueryRunner( dataSource );

    @Override
    public List<Post> selectList(String sql ) throws Exception {
        throw  new Exception("暂未实现的方法");
    }


    /**
     * 批量查询
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    @Override
    public List<Post> selectList(String sql, Object ... params ) throws SQLException {

        //每张表不一样的放，解析结果集
        List<Post> list = run.query(sql, new ResultSetHandler<List<Post>>() {
            @Override
            public List<Post> handle(ResultSet rs) throws SQLException {
                List list = new ArrayList();
                while(rs.next()) {
                    Post post = new Post();

                    post.setId(rs.getInt("id"));
                    post.setAuthorId(rs.getInt("author_id"));
                    post.setChannelId(rs.getInt("channel_id"));
                    post.setComments(rs.getInt("comments"));
                    post.setCreated(rs.getTimestamp("created"));
                    post.setFavors(rs.getInt("favors"));
                    post.setFeatured(rs.getInt("featured"));
                    post.setStatus(rs.getInt("status"));
                    post.setSummary(rs.getString("summary"));
                    post.setTags(rs.getString("tags"));
                    post.setThumbnail(rs.getString("thumbnail"));
                    post.setTitle(rs.getString("title"));
                    post.setViews(rs.getInt("views"));
                    post.setWeight(rs.getInt("weight"));

                    list.add(post);
                }
                return list;
            }
        },params);

        return list;
    }

    /**
     *
     * @param id 查询主键
     * @return  Post 根据主键查询的数据
     * @throws SQLException
     */
    @Override
    public Post selectById(Integer id) throws SQLException {

        //参数列表
        Object[] params = {id};

        String sql = "select id, author_id, channel_id, comments, created, favors, featured, status, summary, tags, thumbnail, title, views, weight from mto_post where id = ?";
        Post post = run.query(sql,new ResultSetHandler<Post>() {
            @Override
            public Post handle(ResultSet rs) throws SQLException {
                Post post = null;
                if(rs.next()) {
                    post = new Post();

                        post.setId(rs.getInt("id"));
                        post.setAuthorId(rs.getInt("author_id"));
                        post.setChannelId(rs.getInt("channel_id"));
                        post.setComments(rs.getInt("comments"));
                        post.setCreated(rs.getTimestamp("created"));
                        post.setFavors(rs.getInt("favors"));
                        post.setFeatured(rs.getInt("featured"));
                        post.setStatus(rs.getInt("status"));
                        post.setSummary(rs.getString("summary"));
                        post.setTags(rs.getString("tags"));
                        post.setThumbnail(rs.getString("thumbnail"));
                        post.setTitle(rs.getString("title"));
                        post.setViews(rs.getInt("views"));
                        post.setWeight(rs.getInt("weight"));
                }
                return  post;
            }
        },params);
        return post;
    }

    /**
    *
    * @param post 待保存的实体对象
    * @return  int 影响行数
    * @throws SQLException
    */
    @Override
    public int save(Post post) throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append(" INSERT INTO mto_post (  ");
        sql.append("  author_id, channel_id, comments, created, favors, featured, status, summary, tags, thumbnail, title, views, weight ");
        sql.append(" )  ");
        sql.append(" VALUES ");
        sql.append("   ( ");
        sql.append("       ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?  ");
        sql.append("   )  ");

        Object[] params = {
            
            post.getAuthorId(),  
            post.getChannelId(),  
            post.getComments(),  
            post.getCreated(),  
            post.getFavors(),  
            post.getFeatured(),  
            post.getStatus(),  
            post.getSummary(),  
            post.getTags(),  
            post.getThumbnail(),  
            post.getTitle(),  
            post.getViews(),  
            post.getWeight()
        };

         int count = run.update(sql.toString(),params);
        //获取主键
        String keySql ="SELECT   @@identity";
        BigInteger key = (BigInteger) run.query(keySql,new ScalarHandler(1));
        post.setId(key.longValue());

        return count;
    }


    /**
    * 根据主键更新
    * @param  post
    * @return
    * @throws SQLException
    */
    @Override
    public int update(Post post) throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append(" UPDATE 				");
        sql.append("   mto_post           ");
        sql.append(" SET                    ");
            
            sql.append("   author_id  = ?,  ");
            sql.append("   channel_id  = ?,  ");
            sql.append("   comments  = ?,  ");
            sql.append("   created  = ?,  ");
            sql.append("   favors  = ?,  ");
            sql.append("   featured  = ?,  ");
            sql.append("   status  = ?,  ");
            sql.append("   summary  = ?,  ");
            sql.append("   tags  = ?,  ");
            sql.append("   thumbnail  = ?,  ");
            sql.append("   title  = ?,  ");
            sql.append("   views  = ?,  ");
            sql.append("   weight  = ?");

        sql.append(" WHERE id= ?            ");

        Object[] params = {
                
                post.getAuthorId(), 
                post.getChannelId(), 
                post.getComments(), 
                post.getCreated(), 
                post.getFavors(), 
                post.getFeatured(), 
                post.getStatus(), 
                post.getSummary(), 
                post.getTags(), 
                post.getThumbnail(), 
                post.getTitle(), 
                post.getViews(), 
                post.getWeight(), 
            post.getId()
        };
        return run.update(sql.toString(),params);

    }

    @Override
    public int delete(Integer id) throws SQLException {
        String sql = "delete from mto_post where id = ?";
        Object[] params = {id};
        return run.update(sql,params);
    }

}
